package OracleDB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;





/*package OracleDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class Zip {
	private Connection conn;

	public Zip() {
		String url = "jdbc:oracle:thin:@127.0.0.1:1521";
		String user = "hr";
		String password = "hr";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			

			// 2) 오라클 DB와 연결
			Connection conn = DriverManager.getConnection(url, user, password);

			

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	public Vector<ZipCode> getZip(String dong){
		Vector<ZipCode> v = new Vector<ZipCode>();
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		try{
			
		String sql = "select *from zipcode2 where dong like ?";
		pstmt= conn.prepareStatement(sql);
		 
		 pstmt.setString(1, "%"+dong+"%");
				
				 rs = pstmt.executeQuery();
				
				while(rs.next()){
					ZipCode zc = new ZipCode();
					zc.setZipcode(rs.getString("zipcode"));
					zc.setSido(rs.getString("sido"));
					zc.setGugun(rs.getString("gugun"));
					zc.setDong(rs.getString("dong"));
					zc.setBunji(rs.getString("bunji"));
					v.add(zc);
				}
			
	}catch(Exception ex){
		ex.printStackTrace();
	}finally{
		try{
			rs.close();
			pstmt.close();
			conn.close();
			
			
		}catch(Exception ex2){
			ex2.printStackTrace();
		}
		return v;
	}
	}

	public static void main(String[] args) {
		Zip z = new Zip();
		Vector<ZipCode> myZip = z.getZip("강");
		for (int i = 0; i < myZip.size(); i++) {
			System.out.println(myZip.get(i));
		}

	}
} */
//------------------

public class Zip {

	private Connection conn;

	public Zip() {
		// Zip 클래스가 만들어지는 순간!
		// 드라이버 로딩, 커넥션 등등 처리

		String url = "jdbc:oracle:thin:@127.0.0.1:1521";
		String user = "hr";
		String password = "hr";

		try {
			// 1) 오라클 드라이버 로딩
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// 2) 오라클 DB와 연결 (connection 생성)
			// Connection이 Interface 이므로 new로 할 수 없음
			conn = DriverManager.getConnection(url, user, password);

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	public Vector<ZipCode> getZip(String dong) {
		Vector<ZipCode> v = new Vector<ZipCode>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			// 3) Statement 생성
			String sql = "SELECT * FROM zipcode WHERE dong like ?";
			pstmt = conn.prepareStatement(sql);

			// parameterIndex 는 ?의 순번. 1부터 시작
			//
			// 이렇게 쓸 수 있어서 PreparedStatement이 편한 거임.
			//
			// pstmt.setInt(1, 5000);
			pstmt.setString(1, "%" + dong + "%"); // 숫자는 setInt()로 넣어줘야 함. ㄷㄷ

			// 4) Statement 실행
			rs = pstmt.executeQuery();

			// 5) 실행결과 출력
			while (rs.next()) {
				ZipCode zc = new ZipCode();
				zc.setZipcode(rs.getString("zipcode"));
				zc.setSido(rs.getString("sido"));
				zc.setGugun(rs.getString("gugun"));
				zc.setDong(rs.getString("dong"));
				zc.setBunji(rs.getString("bunji"));
				v.add(zc);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();

			} catch (Exception ex2) {
				ex2.printStackTrace();
			}
			return v;
		}

	}

	public static void main(String[] args) {
		Zip z = new Zip();
		Vector<ZipCode> myZip = z.getZip("수유");
		for(int i=0; i<myZip.size(); i++) {
			System.out.println(myZip.get(i));
		}
		
		// System.out.println(myZip);

	}

}

